﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using System.Web;
using System.Net.Http;
using System.Net;

namespace DrawManage.Tools
{
    public class BaseDao
    {
        //public static string ConnectionString = "Data Source=115.28.208.24;Initial Catalog=Deqi;Persist Security Info=True;User ID=sa;Password=xxxx";
        //public static string ConnectionString = "Data Source=127.0.0.1;Initial Catalog=AgingTest;Persist Security Info=True;User ID=sa;Password=dyzn123456";
        public static string ConnectionString = "Data Source=192.168.1.83;Initial Catalog=Draw;Persist Security Info=True;User ID=sa;Password=dyzn123456";

        public static SqlConnection getConnection()
        {
            SqlConnection conn = new SqlConnection(ConnectionString);
            return conn;
        }

        /***************直接执行一条sql，返回影响的行数***************/
        /// <summary>
        /// 运行sql
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns>返回影响的条数</returns>
        public static int execute(string sql)
        {
            SqlCommand command = new SqlCommand(sql);//SDFASDF
            SqlConnection sqlConn = getConnection();
            command.Connection = sqlConn;
            if (sqlConn.State != ConnectionState.Open)
            {
                sqlConn.Open();
            }
            SqlDataReader rd = command.ExecuteReader();
            //int count = command.ExecuteReader();//.ExecuteNonQuery();
            int i = 0;
            while (rd.Read())
            {
                i++;
            }
            rd.Close();
            rd.Dispose();
            sqlConn.Close();
            sqlConn.Dispose();
            return i;
        }

        public void CheckConnection()
        {
            //SqlCommand command = new SqlCommand();//SDFASDF
            SqlConnection sqlConn = getConnection();
            //command.Connection = sqlConn;
            if (sqlConn.State != ConnectionState.Open)
            {
                sqlConn.Open();
            }
            sqlConn.Close();
            sqlConn.Dispose();
        }

        /// <summary>
        /// 保存
        /// </summary>
        /// <param name="table">表名</param>
        /// <param name="msg">键值对</param>
        /// <returns>影响的行数</returns>
        public int save(string table, Dictionary<string, object> msg)
        {
            string[] properties = (string[])msg.Keys.ToArray();
            string sql = "INSERT INTO [" + table + "] ";
            string setPro = "(";
            foreach (string key in msg.Keys)
            {
                if (key != "mapID")
                    setPro += "[" + key + "], ";
            }

            setPro = setPro.Substring(0, setPro.Length - 2);
            setPro += ") VALUES(";

            foreach (object value in msg.Values)
            {
                if (value == null)
                {
                    setPro += "null, ";
                }
                else if (Object.ReferenceEquals(value.GetType(), new int().GetType()) || Object.ReferenceEquals(value.GetType(), new bool().GetType()))
                {
                    setPro += value + ", ";
                }
                else
                {
                    setPro += ("'" + value + "', ");
                }

            }
            setPro = setPro.Substring(0, setPro.Length - 2);
            setPro += ")";
            sql += setPro;
            System.Diagnostics.Debug.Write("[sql]:" + sql);

            SqlCommand command = new SqlCommand(sql);
            SqlConnection sqlConn = getConnection();
            command.Connection = sqlConn;
            if (sqlConn.State != ConnectionState.Open)
            {
                sqlConn.Open();
            }
            int count = command.ExecuteNonQuery();
            sqlConn.Close();
            sqlConn.Dispose();
            return count;
        }


        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="table">表名</param>
        /// <param name="msg">键值对</param>
        /// <param name="condition">条件</param>
        /// <returns></returns>
        public int update(String table, Dictionary<string, object> msg, string condition)
        {

            string[] properties = (string[])msg.Keys.ToArray();
            string sql = "UPDATE [" + table + "] SET ";
            string setPro = "";
            foreach (KeyValuePair<string, object> kvp in msg)
            {
                if (kvp.Value != null && kvp.Key != "mapID")
                {
                    setPro += kvp.Key + " = ";
                    if (Object.ReferenceEquals(kvp.Value.GetType(), new int().GetType()) || Object.ReferenceEquals(kvp.Value.GetType(), new bool().GetType()))
                    {
                        setPro += kvp.Value + ", ";
                    }
                    else
                    {
                        setPro += ("'" + kvp.Value + "', ");
                    }
                }
            }

            setPro = setPro.Substring(0, setPro.Length - 2);
            setPro += " WHERE 1=1 AND " + condition;
            sql += setPro;

            SqlCommand command = new SqlCommand(sql);
            SqlConnection sqlConn = getConnection();
            command.Connection = sqlConn;
            if (sqlConn.State != ConnectionState.Open)
            {
                sqlConn.Open();
            }
            int count = command.ExecuteNonQuery();
            sqlConn.Close();
            sqlConn.Dispose();
            return count;
        }


        public List<Dictionary<string, object>> GetList(String sql, String[] properties)
        {
            int prosLength = properties.Length;
            SqlCommand command = new SqlCommand(sql);
            SqlConnection sqlConn = getConnection();
            command.Connection = sqlConn;
            if (sqlConn.State != ConnectionState.Open)
            {
                sqlConn.Open();
            }
            SqlDataReader read = command.ExecuteReader();
            List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
            Dictionary<string, object> map = null;
            while (read.Read())
            {
                map = new Dictionary<string, object>();
                for (int i = 0; i < prosLength; i++)
                {
                    map.Add(properties[i], read[properties[i]]);
                }
                list.Add(map);
                map = null;

            }
            read.Close();
            sqlConn.Close();
            read.Dispose();
            sqlConn.Dispose();
            return list;
        }


        /// <summary>
        /// 返回目标表内单独字段
        /// </summary>
        /// <param name="table"></param>
        /// <param name="field"></param>
        /// <param name="Condition"></param>
        /// <returns></returns>
        public string GetString(string table, string field, string Condition)
        {
            string sql = "select " + field + " from " + table + " where 1=1 " + Condition;
            SqlCommand command = new SqlCommand(sql);
            SqlConnection sqlConn = getConnection();
            command.Connection = sqlConn;
            if (sqlConn.State != ConnectionState.Open)
            {
                sqlConn.Open();
            }
            SqlDataReader read = command.ExecuteReader();
            string ReturnStr = "";
            while (read.Read())
            {
                ReturnStr = read[field].ToString();


            }
            read.Close();
            sqlConn.Close();
            read.Dispose();
            sqlConn.Dispose();
            return ReturnStr;
        }

        public static string GetSqlByInsert(string table, string[] column, string[] values)
        {
            string sql = "";
            string tempcolumn = "";
            string tempvalues = "";
            for (int i = 0; i < column.Length; i++)
            {
                tempcolumn += column[i] + ",";
            }
            tempcolumn = tempcolumn.Substring(0, tempcolumn.Length - 1);

            for (int i = 0; i < values.Length; i++)
            {
                tempvalues += "'" + values[i] + "',";
            }
            tempvalues = tempvalues.Substring(0, tempvalues.Length - 1);
            sql = "insert into " + table + "(" + tempcolumn + ") values(" + tempvalues + ");";
            return sql;

        }



    }
}